#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：tools 
@File ：allocation_algorithm.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2025/11/26
@Description: 物料-合同智能分配算法
"""

import pandas as pd
import logging
from pathlib import Path
from typing import Dict, List, Tuple, Optional
from datetime import datetime


class AllocationAlgorithm:
	"""
	物料-合同智能分配算法
	
	优先级策略：
	1. 完全匹配（无需切割）- 优先级1
	2. 吨重切割（仅重量切割）- 优先级2
	3. 双向切割（重量+宽度切割）- 优先级3
	
	损失计算：
	- 切割次数权重：1000
	- 损失率权重：100
	- 总损失 = 切割次数 × 1000 + 综合损失率 × 100
	
	约束条件：
	- 物料规格只能减少，不能增加
	- 一个物料只分配给一个合同
	- 一个合同只由一个物料满足
	"""
	
	def __init__(self, result_file_path: Path):
		"""
		初始化分配算法
		:param result_file_path: result.xlsx文件路径
		"""
		self.result_file_path = result_file_path
		self.sheet1_df = None  # 完全匹配
		self.sheet2_df = None  # 无吨重筛选
		self.sheet3_df = None  # 规格不含宽度
		
		self.candidates = []  # 候选匹配列表
		self.allocated_materials = set()  # 已分配物料
		self.allocated_contracts = set()  # 已分配合同
		self.allocation_results = []  # 分配结果
		
	def load_data(self):
		"""
		加载匹配结果数据
		"""
		try:
			logging.info(f"正在加载匹配结果文件：{self.result_file_path}")
			
			# 读取三个Sheet
			self.sheet1_df = pd.read_excel(self.result_file_path, sheet_name='完整筛选')
			self.sheet2_df = pd.read_excel(self.result_file_path, sheet_name='无吨重筛选')
			self.sheet3_df = pd.read_excel(self.result_file_path, sheet_name='规格不含宽度')
			
			logging.info(f"Sheet1（完全匹配）：{len(self.sheet1_df)} 条")
			logging.info(f"Sheet2（无吨重筛选）：{len(self.sheet2_df)} 条")
			logging.info(f"Sheet3（规格不含宽度）：{len(self.sheet3_df)} 条")
			
			return True
		except Exception as e:
			logging.error(f"加载数据失败：{e}")
			return False
	
	def calculate_loss(self, material_row: pd.Series, priority: int) -> Tuple[int, float, float, float]:
		"""
		计算切割损失
		:param material_row: 物料行数据
		:param priority: 优先级（1/2/3）
		:return: (切割次数, 重量损失率, 宽度损失率, 综合损失)
		"""
		# 切割次数
		cut_times = priority - 1  # 优先级1=0次，2=1次，3=2次
		
		# 获取物料信息（安全访问）
		material_weight = material_row.get('重量(t)', 0)
		material_width = material_row.get('宽(mm)', 0)
		
		# 重量损失率和宽度损失率初始化
		weight_loss_rate = 0.0
		width_loss_rate = 0.0
		
		# 从合同号列中提取第一个合同号（假设合同号1是最优的）
		# 注意：这里需要从合同数据中获取对应的需求值
		# 由于匹配结果中没有合同的详细需求，我们假设损失率为0（完全匹配）
		# 实际应用中，需要关联合同数据获取准确的需求值
		
		# 简化处理：根据优先级估算损失率
		if priority == 1:
			# 完全匹配，无损失
			weight_loss_rate = 0.0
			width_loss_rate = 0.0
		elif priority == 2:
			# 吨重切割，假设有一定的重量损失，宽度无损失
			weight_loss_rate = 0.05  # 5%的估算损失
			width_loss_rate = 0.0
		else:  # priority == 3
			# 双向切割，重量和宽度都有损失
			weight_loss_rate = 0.05
			width_loss_rate = 0.05
		
		# 综合损失率
		avg_loss_rate = (weight_loss_rate + width_loss_rate) / 2
		
		# 总损失（切割次数权重1000 + 损失率权重100）
		total_loss = cut_times * 1000 + avg_loss_rate * 100
		
		return cut_times, weight_loss_rate, width_loss_rate, total_loss
	
	def build_candidate_list(self):
		"""
		构建候选匹配列表
		"""
		logging.info("开始构建候选匹配列表...")
		self.candidates = []
		
		# 处理Sheet1（优先级1：完全匹配）
		for idx, row in self.sheet1_df.iterrows():
			material_id = row.get('材料号', f'unknown_{idx}')
			# 提取合同号（可能有多个，取第一个）
			contract_numbers = self._extract_contract_numbers(row)
			
			for contract_no in contract_numbers:
				cut_times, weight_loss, width_loss, total_loss = self.calculate_loss(row, priority=1)
				
				self.candidates.append({
					'材料号': material_id,
					'合同号': contract_no,
					'优先级': 1,
					'优先级名称': '完全匹配',
					'切割次数': cut_times,
					'重量损失率': weight_loss,
					'宽度损失率': width_loss,
					'总损失': total_loss,
					'物料数据': row.to_dict()
				})
		
		# 处理Sheet2（优先级2：吨重切割）
		for idx, row in self.sheet2_df.iterrows():
			material_id = row.get('材料号', f'unknown_{idx}')
			contract_numbers = self._extract_contract_numbers(row)
			
			for contract_no in contract_numbers:
				cut_times, weight_loss, width_loss, total_loss = self.calculate_loss(row, priority=2)
				
				self.candidates.append({
					'材料号': material_id,
					'合同号': contract_no,
					'优先级': 2,
					'优先级名称': '吨重切割',
					'切割次数': cut_times,
					'重量损失率': weight_loss,
					'宽度损失率': width_loss,
					'总损失': total_loss,
					'物料数据': row.to_dict()
				})
		
		# 处理Sheet3（优先级3：双向切割）
		for idx, row in self.sheet3_df.iterrows():
			material_id = row.get('材料号', f'unknown_{idx}')
			contract_numbers = self._extract_contract_numbers(row)
			
			for contract_no in contract_numbers:
				cut_times, weight_loss, width_loss, total_loss = self.calculate_loss(row, priority=3)
				
				self.candidates.append({
					'材料号': material_id,
					'合同号': contract_no,
					'优先级': 3,
					'优先级名称': '双向切割',
					'切割次数': cut_times,
					'重量损失率': weight_loss,
					'宽度损失率': width_loss,
					'总损失': total_loss,
					'物料数据': row.to_dict()
				})
		
		logging.info(f"候选匹配列表构建完成，共 {len(self.candidates)} 个候选匹配")
	
	def _extract_contract_numbers(self, row: pd.Series) -> List[str]:
		"""
		从行数据中提取合同号列表
		:param row: 行数据
		:return: 合同号列表
		"""
		contract_numbers = []
		
		# 查找所有合同号列（合同号1, 合同号2, ...）
		for col in row.index:
			if col.startswith('合同号') and pd.notna(row[col]) and str(row[col]).strip():
				contract_numbers.append(str(row[col]).strip())
		
		return contract_numbers
	
	def sort_candidates(self):
		"""
		对候选列表排序
		排序规则：总损失从小到大（优先级高的损失小）
		"""
		self.candidates.sort(key=lambda x: x['总损失'])
		logging.info("候选列表排序完成")
	
	def allocate(self):
		"""
		执行贪心分配算法
		"""
		logging.info("开始执行智能分配...")
		
		self.allocated_materials = set()
		self.allocated_contracts = set()
		self.allocation_results = []
		
		for candidate in self.candidates:
			material_id = candidate['材料号']
			contract_no = candidate['合同号']
			
			# 检查是否已分配
			if material_id in self.allocated_materials:
				continue
			if contract_no in self.allocated_contracts:
				continue
			
			# 执行分配
			self.allocation_results.append(candidate)
			self.allocated_materials.add(material_id)
			self.allocated_contracts.add(contract_no)
		
		logging.info(f"分配完成！成功分配 {len(self.allocation_results)} 对")
	
	def generate_report(self) -> Dict[str, pd.DataFrame]:
		"""
		生成分配报告
		:return: 包含多个Sheet的字典
		"""
		logging.info("开始生成分配报告...")
		
		# Sheet1: 分配结果
		allocation_df = self._build_allocation_dataframe()
		
		# Sheet2: 未分配物料
		unallocated_materials_df = self._build_unallocated_materials()
		
		# Sheet3: 未满足合同
		unallocated_contracts_df = self._build_unallocated_contracts()
		
		# Sheet4: 分配统计
		statistics_df = self._build_statistics()
		
		return {
			'分配结果': allocation_df,
			'未分配物料': unallocated_materials_df,
			'未满足合同': unallocated_contracts_df,
			'分配统计': statistics_df
		}
	
	def _build_allocation_dataframe(self) -> pd.DataFrame:
		"""
		构建分配结果DataFrame
		"""
		if not self.allocation_results:
			return pd.DataFrame()
		
		rows = []
		for result in self.allocation_results:
			material_data = result['物料数据']
			row = {
				'材料号': result['材料号'],
				'合同号': result['合同号'],
				'优先级': result['优先级'],
				'匹配方式': result['优先级名称'],
				'切割次数': result['切割次数'],
				'重量损失率(%)': f"{result['重量损失率'] * 100:.2f}",
				'宽度损失率(%)': f"{result['宽度损失率'] * 100:.2f}",
				'总损失评分': f"{result['总损失']:.2f}",
			}
			# 添加物料的其他信息
			for key, value in material_data.items():
				if key not in row:
					row[key] = value
			
			rows.append(row)
		
		return pd.DataFrame(rows)
	
	def _build_unallocated_materials(self) -> pd.DataFrame:
		"""
		构建未分配物料列表
		"""
		# 收集所有物料
		all_materials = set()
		for df in [self.sheet1_df, self.sheet2_df, self.sheet3_df]:
			if df is not None and not df.empty:
				all_materials.update(df['材料号'].dropna().unique())
		
		# 找出未分配的
		unallocated = all_materials - self.allocated_materials
		
		if not unallocated:
			return pd.DataFrame({'说明': ['所有物料均已分配']})
		
		# 从原始数据中提取未分配物料的信息
		unallocated_rows = []
		for df in [self.sheet1_df, self.sheet2_df, self.sheet3_df]:
			if df is not None and not df.empty:
				mask = df['材料号'].isin(unallocated)
				unallocated_rows.append(df[mask])
		
		if unallocated_rows:
			result_df = pd.concat(unallocated_rows, ignore_index=True)
			# 去重
			result_df = result_df.drop_duplicates(subset=['材料号'])
			return result_df
		
		return pd.DataFrame({'材料号': list(unallocated)})
	
	def _build_unallocated_contracts(self) -> pd.DataFrame:
		"""
		构建未满足合同列表
		"""
		# 收集所有合同号
		all_contracts = set()
		for df in [self.sheet1_df, self.sheet2_df, self.sheet3_df]:
			if df is not None and not df.empty:
				for col in df.columns:
					if col.startswith('合同号'):
						all_contracts.update(df[col].dropna().astype(str).unique())
		
		# 找出未分配的
		unallocated = all_contracts - self.allocated_contracts
		
		if not unallocated:
			return pd.DataFrame({'说明': ['所有合同均已满足']})
		
		return pd.DataFrame({'合同号': sorted(list(unallocated))})
	
	def _build_statistics(self) -> pd.DataFrame:
		"""
		构建分配统计信息
		"""
		if not self.allocation_results:
			return pd.DataFrame({'说明': ['无分配结果']})
		
		# 按优先级统计
		priority_stats = {}
		for result in self.allocation_results:
			priority = result['优先级名称']
			if priority not in priority_stats:
				priority_stats[priority] = {
					'数量': 0,
					'总重量(t)': 0,
					'平均重量损失率(%)': [],
					'平均宽度损失率(%)': []
				}
			
			priority_stats[priority]['数量'] += 1
			material_weight = result['物料数据'].get('重量(t)', 0)
			priority_stats[priority]['总重量(t)'] += material_weight
			priority_stats[priority]['平均重量损失率(%)'].append(result['重量损失率'] * 100)
			priority_stats[priority]['平均宽度损失率(%)'].append(result['宽度损失率'] * 100)
		
		# 构建统计表
		rows = []
		for priority_name, stats in priority_stats.items():
			avg_weight_loss = sum(stats['平均重量损失率(%)']) / len(stats['平均重量损失率(%)']) if stats['平均重量损失率(%)'] else 0
			avg_width_loss = sum(stats['平均宽度损失率(%)']) / len(stats['平均宽度损失率(%)']) if stats['平均宽度损失率(%)'] else 0
			
			rows.append({
				'匹配方式': priority_name,
				'分配数量': stats['数量'],
				'总重量(t)': f"{stats['总重量(t)']:.2f}",
				'平均重量损失率(%)': f"{avg_weight_loss:.2f}",
				'平均宽度损失率(%)': f"{avg_width_loss:.2f}"
			})
		
		# 添加总计行
		total_count = sum(s['数量'] for s in priority_stats.values())
		total_weight = sum(s['总重量(t)'] for s in priority_stats.values())
		
		rows.append({
			'匹配方式': '总计',
			'分配数量': total_count,
			'总重量(t)': f"{total_weight:.2f}",
			'平均重量损失率(%)': '-',
			'平均宽度损失率(%)': '-'
		})
		
		return pd.DataFrame(rows)
	
	def run(self, output_path: Optional[Path] = None) -> bool:
		"""
		执行完整的分配流程
		:param output_path: 输出文件路径，默认为桌面的allocation_result.xlsx
		:return: 是否成功
		"""
		try:
			# 1. 加载数据
			if not self.load_data():
				return False
			
			# 2. 构建候选列表
			self.build_candidate_list()
			
			if not self.candidates:
				logging.warning("没有可用的候选匹配，无法进行分配")
				return False
			
			# 3. 排序
			self.sort_candidates()
			
			# 4. 分配
			self.allocate()
			
			# 5. 生成报告
			report = self.generate_report()
			
			# 6. 保存结果
			if output_path is None:
				desktop = Path.home() / 'Desktop'
				output_path = desktop / f'allocation_result_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx'
			
			with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
				for sheet_name, df in report.items():
					df.to_excel(writer, sheet_name=sheet_name, index=False)
			
			logging.info(f"分配结果已保存到：{output_path}")
			
			# 返回统计信息
			logging.info("=" * 50)
			logging.info("分配统计汇总：")
			logging.info(f"总分配数量：{len(self.allocation_results)}")
			logging.info(f"未分配物料：{len(self._build_unallocated_materials())}")
			logging.info(f"未满足合同：{len(self._build_unallocated_contracts())}")
			logging.info("=" * 50)
			
			return True
			
		except Exception as e:
			logging.error(f"分配过程中发生错误：{e}", exc_info=True)
			return False


def run_allocation(result_file_path: Path, output_path: Optional[Path] = None) -> bool:
	"""
	运行智能分配算法（便捷接口）
	:param result_file_path: result.xlsx文件路径
	:param output_path: 输出文件路径
	:return: 是否成功
	"""
	algo = AllocationAlgorithm(result_file_path)
	return algo.run(output_path)


if __name__ == '__main__':
	# 测试代码
	logging.basicConfig(
		level=logging.INFO,
		format='%(asctime)s - %(levelname)s - %(message)s'
	)
	
	# 示例用法
	result_file = Path.home() / 'Desktop' / 'result.xlsx'
	if result_file.exists():
		run_allocation(result_file)
	else:
		print(f"测试文件不存在：{result_file}")
